# -*- coding: utf-8 -*-

import pymysql
import pandas as pd
import csv
import re
import numpy as np
from datetime import datetime

# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="hui123456", db='dbtest')
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()

sql1 = """
       SELECT shop,
              product_BianMa,
              SUM(Product_num),
              SUM(Product_price),
              SUM(yh_pingtai)
       FROM niandu_day
       WHERE
           MONTH (day) = 10
         AND (
           order_zt = "完成"
          OR order_zt = "等待出库"
          OR order_zt = "等待确认收货"
          OR order_zt = "已签收"
          OR order_zt = "待配货"
          OR order_zt = "待发货"
          OR order_zt = "交易成功"
          OR order_zt = "已完成"
          OR order_zt = "卖家已发货，等待买家确认"
          OR order_zt = "买家已付款，等待卖家发货"
          OR order_zt = "已发货"
          OR order_zt = "调度中"
          OR order_zt = "已收货"
          OR order_zt = "已发货未签收"
          OR order_zt = "已发货，待签收"
          OR order_zt = "(锁定)等待确认收货"
          OR order_zt = "已发货，待收货"
          OR order_zt = "(锁定)等待确认收货"
          OR order_zt = "部分发货"
          OR order_zt = "买家已付款,等待卖家发货"
          OR order_zt = "卖家已发货"
          OR order_zt = "买家已付款"
          OR order_zt = "发货即将超时"
          OR order_zt = "卖家部分发货"
          OR order_zt = "待买家收货"
          OR order_zt = "待卖家发货"
          OR order_zt = "部分发货中"
           )
         AND order_sh != "退款成功"
         AND order_sh != "售后完成"
         AND order_sh != "退款完成"
         AND order_sh != "退货退款完成 "
       GROUP BY
           shop,
           product_BianMa

       """

cursor.execute(sql1)
result1 = cursor.fetchall()
xs_xs = pd.DataFrame(list(result1))
xs_xs.columns = ['店铺', '编码', '商品数量', '应付', '平台优惠']
xs_xs.to_excel(r'G:\工作\SKU利润测算\2025年\10月\10月利润测算明细_线上.xlsx')


sql11 = """
        SELECT *
        FROM doudian_day
        WHERE
            MONTH (day) = 10
          AND dianzhuguo = "电煮锅赠品"
          AND (
            order_zt = "已签收"
           OR order_zt = "待发货"
           OR order_zt = "已完成"
           OR order_zt = "已发货"
           OR order_zt = "已支付"
            )
          AND order_sh != "退款成功"
          AND order_sh != "售后完成"
          AND order_sh != "退款完成"
          AND order_sh != "已全额退款" 
        """
cursor.execute(sql11)
result11 = cursor.fetchall()
dg = pd.DataFrame(list(result11))
# dg.columns=['店铺','编码','商品数量','应付','平台优惠']
dg.to_excel(r'G:\工作\SKU利润测算\2025年\10月\10月锅.xlsx')

sql2 = """
       SELECT product_BianMa,
              SUM(Product_num),
              SUM(Product_price),
              SUM(yh_pingtai),
              SUM(yh_daren)
       FROM doudian_day
       WHERE
           MONTH (day) = 10
         AND (
           order_zt = "已签收"
          OR order_zt = "待发货"
          OR order_zt = "已完成"
          OR order_zt = "已发货"
          OR order_zt = "已支付"
           )
         AND order_sh != "退款成功"
         AND order_sh != "售后完成"
         AND order_sh != "退款完成"
         AND order_sh != "已全额退款"
       GROUP BY
           product_BianMa

       """

sql22 = """
        SELECT product_BianMa,
               Product_dj,
               SUM(Product_num),
               SUM(Product_price),
               SUM(yh_pingtai)
        FROM doudian_day
        WHERE
            MONTH (day) = 10
          AND (
            order_zt = "已签收"
           OR order_zt = "待发货"
           OR order_zt = "已完成"
           OR order_zt = "已发货"
           OR order_zt = "已支付"
            )
          AND order_sh != "退款成功"
          AND order_sh != "售后完成"
          AND order_sh != "退款完成"
          AND order_sh != "已全额退款"
        GROUP BY
            product_BianMa,
            Product_dj


        """

cursor.execute(sql2)
result2 = cursor.fetchall()

jg = pd.DataFrame(list(result2))
jg.to_excel(r'G:\工作\SKU利润测算\2025年\10月\10月抖音编码价格.xlsx')

cursor.execute(sql22)
result22 = cursor.fetchall()

dj = pd.DataFrame(list(result22))
dj.to_excel(r'G:\工作\SKU利润测算\2025年\10月\10月抖音编码单价.xlsx')
